﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;
using System.Configuration;
using System.Xml;
using System.Collections;
using System.Data.OleDb;


namespace InsideDL
{
    public class Utility
    {
        private static Hashtable configTable = null;
      
        /// <summary>
        /// 取回目前檔案內容
        /// </summary>
        /// <param name="p_uriFTP">FTP的位置EX: ftp://</param>
        /// <param name="p_id"></param>
        /// <param name="p_pw"></param>
        /// <returns></returns>
        public static string[] GetFTPFileList(string p_uriFTP, string p_id, string p_pw)
        {
            StringBuilder result = new StringBuilder();
            WebResponse response = null;
            try
            {
                FtpWebRequest reqFTP;
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(p_uriFTP));
                reqFTP.UseBinary = true;
                reqFTP.Credentials = new NetworkCredential(p_id, p_pw);
                reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
                reqFTP.Proxy = null;
                reqFTP.KeepAlive = false;
                reqFTP.UsePassive = false;
                response = reqFTP.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream()))
                {
                    string line = reader.ReadLine();
                    while (line != null)
                    {
                        result.Append(line);
                        result.Append("\n");
                        line = reader.ReadLine();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (response != null)
                {
                    response.Close();
                }       
            }
            result.Remove(result.ToString().LastIndexOf('\n'), 1);
            return result.ToString().Split('\n');
        }

        /// <summary>
        /// 取得目前路徑下的檔案
        /// </summary>
        /// <param name="p_path"></param>
        /// <returns></returns>
        public static string[] GetFileList(string p_path)
        {
            string[] files = System.IO.Directory.GetFiles(p_path);
            List<string> accList = new List<string>();
            foreach (string fl in files)
            {
                accList.Add(fl.Replace(p_path, string.Empty)
                    .Replace("\\",string.Empty));
            }
            return accList.ToArray<string>();
        }

        public static bool DownloadFile(string p_uriFTP, string p_id, string p_pw
                                    ,string[] p_fileNames, string p_outPath)
        {  
            bool ok = true;
            try
            {
                WebClient request = new WebClient();
                request.Credentials = new NetworkCredential(p_id, p_pw);
                foreach (string str in p_fileNames)
                {
                    string dlFile = string.Format("{0}/{1}", p_uriFTP, str);
                    byte[] fileData = request.DownloadData(dlFile);
                    FileStream file = File.Create(
                                           string.Format(@"{0}\{1}", p_outPath, str));
                    file.Write(fileData, 0, fileData.Length);
                    file.Close();
                }

            }
            catch (Exception ex)
            {
                ok = false;
                throw new Exception(ex.Message);
            }
            return ok;
        }

        public static void LoadConfigValue(string p_section)
        {
            configTable = new System.Collections.Hashtable();
            string[] tags = ConfigurationManager.AppSettings[p_section]
                .ToString().Split(new char[] { ',' });
            int i = 0;
            //XmlNode node = (XmlNode)System.Configuration.ConfigurationSettings.GetConfig(p_section);            
            XmlNode node = System.Configuration.ConfigurationManager.GetSection(p_section) as XmlNode;
            foreach (XmlNode child in node.ChildNodes)
            {
                try
                {
                    configTable.Add(tags[i].ToString(), child.Attributes[0].Value);
                }
                catch(Exception ex)
                {
                    new Exception(
                        string.Format("LoadConfigValue ERROR MSG:{0}", ex.Message));
                }
                i++;
            }
        }

        public static string GetConfigValue(string p_key)
        {
            string rtnString = string.Empty;
            try
            {
                rtnString = configTable[p_key].ToString();
            }
            catch (Exception ex)
            {
                new Exception(
                    string.Format("所輸入的值不在HashTable 中 GetConfigValue:{0}", ex.Message));
            }
            return rtnString;
        }

        public static string GetCurrentYM(DateTime p_date)
        {
            string YYYY, MM;
            //DateTime date1 = p_date == null ? DateTime.Now : p_date;
            YYYY = p_date.Year.ToString();
            //YYYY = Convert.ToString(DateTime.Now.Year + 1); //for test
            MM = string.Format("0{0}", p_date.Month.ToString());
            MM = MM.Length == 3 ? MM.Substring(1) : MM;
            return YYYY + MM;
            
        }

        public static string GetCurrentYM()
        {
            return GetCurrentYM(DateTime.Now);
        }

        public static System.Data.DataTable GetData(string p_sql, string p_path)
        {
            string connectionString;
            connectionString = string.Format(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=""TEXT;HDR=Yes;FMT=Delimited"";"
                      , p_path);
            OleDbConnection cn = new OleDbConnection();
            System.Data.DataSet CSVDataSet = new System.Data.DataSet();
            cn.ConnectionString = connectionString; //strPath是檔案所在的資料夾
            OleDbDataAdapter CSVAdapter = new OleDbDataAdapter(p_sql, cn);
            CSVAdapter.Fill(CSVDataSet, "CSV");
            cn.Close();
            return CSVDataSet.Tables[0];
        }

//        protected static System.Data.DataTable GetCSVData(string p_sql, string p_path)
//        {
//            string connectionString;
//            connectionString = string.Format(
//                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=""TEXT;HDR=Yes;FMT=Delimited"";"
//                      , p_path);
//            return GetData(p_sql, connectionString, "CSV");    
//        }

//        protected static System.Data.DataTable GetExcelData(string p_sql, string p_path)
//        {
//            string connection = string.Format( 
//                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
//                Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""",p_path) ;
//            return GetData(p_sql, connection, p_path);
//        }

        public static System.Data.DataTable GetData(string p_sql
            , string p_connectionString, string p_tableName)
        {
            OleDbConnection cn = new OleDbConnection();
            //System.Data.DataSet CSVDataSet = new System.Data.DataSet();
            cn.ConnectionString = p_connectionString; //strPath是檔案所在的資料夾
            OleDbCommand cmd = cn.CreateCommand();
            cn.Open();
            cmd.CommandText = p_sql;
            System.Data.DataTable dt = new System.Data.DataTable();

            dt.Load(cmd.ExecuteReader());
            //OleDbDataAdapter CSVAdapter = new OleDbDataAdapter(p_sql, cn);
            //CSVAdapter.Fill(CSVDataSet, p_tableName);
            cn.Close();
            return dt;
        }


        /// <summary>
        /// 傳回9912 或 9901
        /// </summary>
        /// <returns></returns>
        public static string GetTWYM()
        {
            //string yyyy, mm;
            //yyyy = Convert.ToString(DateTime.Now.AddMonths(-1).Year - 1911);
            //mm = "0" + Convert.ToString(DateTime.Now.AddMonths(-1).Month );
            //mm = mm.Length == 3 ? mm.Substring(1) : mm;
            //return yyyy+mm; 
            return Utility.GetTWYM(DateTime.Now);
        }

        public static string GetTWYM(DateTime p_date)
        {
            string yyyy, mm;
            yyyy = Convert.ToString(p_date.AddMonths(-1).Year - 1911);
            mm = "0" + Convert.ToString(p_date.AddMonths(-1).Month);
            mm = mm.Length == 3 ? mm.Substring(1) : mm;
            return yyyy + mm;         
        }

        public static string GetCurrentDay(string p_csvFile)
        {
            //Convert.ToInt16(p_csvFile.Substring(12, 2)) - 1;
            DateTime mydates = DateTime.Now;
            string rtnDate = string.Empty;
            if (p_csvFile.Length > 11)
            {
                mydates = Convert.ToDateTime(string.Format("{0}-{1}-1"
                        , p_csvFile.Substring(8, 4)
                        , p_csvFile.Substring(12, 2)));
                rtnDate = mydates.AddDays(-1).Date.Day.ToString();
            
            }
            return rtnDate;
            //return DateTime.Now.Day.ToString();
        }

        /// <summary>
        /// 取回目前月份之上個月的總日數
        /// </summary>
        /// <returns></returns>
        public static string GetCurrentDay()
        {
            DateTime mydates = Convert.ToDateTime(string.Format("{0}-{1}-1"
                    , DateTime.Now.Year.ToString()
                    , DateTime.Now.Month.ToString()));
            return mydates.AddDays(-1).Day.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_currency">YYYYMM</param>
        /// <returns></returns>
        public static string GetTWYM(string p_currency)
        {
            string strCurrency;
            strCurrency = string.Format("{0}-{1}-01",p_currency.Substring(0,4)
                ,p_currency.Substring(4,2));            
            return Utility.GetTWYM(Convert.ToDateTime(strCurrency));
        }

        public static System.Data.DataTable GetSheets(string p_connectionString)
        {
            string sql = "select * from [ExcelSheets$]";
            return GetData(sql, p_connectionString, "EXCEL_SHEETS");
        }

        /// <summary>
        /// 從 CSV 中去拆出來這個月和上個月的月份要填入到會計檔中
        /// </summary>
        /// <param name="p_csvFile"></param>
        /// <returns></returns>
        public static string[] GetThisLastMonth(string p_csvFile)
        {
            //ARTM95A_20101102.csv
            int last;
            string sLast, before;
            sLast = string.Empty;
            before = string.Empty;
            if (p_csvFile.Length > 11)
            {
                last = Convert.ToInt16(p_csvFile.Substring(12, 2)) - 1;
                last = last.Equals(0) ? 12 : last;
                before = "0" + Convert.ToString(last - 1);
                //當遇到before 為00時，表示上個月要用12，這個月是1月
                before = before.Equals("00") & last.Equals(1) ? "12" : before;
                sLast = "0" + last.ToString();
                sLast = sLast.Length == 3 ? sLast.Substring(1) : sLast;
                before = before.Length == 3 ? before.Substring(1) : before;
            
            }            
            return new string[] {sLast,before };
        }

        /// <summary>
        /// 取回有台灣年月
        /// </summary>
        /// <param name="p_csvFile"></param>
        /// <param name="p_y"></param>
        /// <returns></returns>
        public static string[] GetThisLastMonth(string p_csvFile,string p_y)
        {
            //ARTM95A_20101102.csv
            //p_csvFile = "ARTM95A_20110202.csv";
            int last,last_Y,before_Y;
            last = Convert.ToInt16(p_csvFile.Substring(12, 2)) - 1;
            last = last.Equals(0) ? 12 : last;
            string before = "0" + Convert.ToString( last.Equals(1) ? 12 : last - 1);
            string sLast = "0" + last.ToString();
            sLast = sLast.Length == 3 ? sLast.Substring(1) : sLast;
            before = before.Length == 3 ? before.Substring(1) : before;

            last_Y = Convert.ToDateTime(string.Format("{0}-{1}-01", p_csvFile.Substring(8, 4)
                , p_csvFile.Substring(12, 2))).AddDays(-1).Year-1911;

            before_Y = Convert.ToDateTime(string.Format("{0}-{1}-01", p_csvFile.Substring(8, 4)
                            , p_csvFile.Substring(12, 2))).AddMonths(-1).AddDays(-1).Year-1911;
            return new string[] { 
                string.Format("{0}.{1}",last_Y.ToString(),sLast) 
                ,string.Format("{0}.{1}",before_Y.ToString(), before)
            };
        }

        public static string[] GetThisLastDay(string p_csvFile)
        {
            string sLast = GetCurrentDay(p_csvFile);
            DateTime mydates = Convert.ToDateTime(string.Format("{0}-{1}-1"
                  , p_csvFile.Substring(8, 4)
                  , p_csvFile.Substring(12, 2))).AddMonths(-1).AddDays(-1);
            string sBefore = mydates.Day.ToString();
            return new string[] { sLast, sBefore };
        }

        /// <summary>
        /// 依檔名傳回YYYY-MM-DD
        /// </summary>
        /// <param name="p_csvFile"></param>
        /// <returns>YYYY-MM-DD</returns>
        public static string GetLastDate(string p_csvFile)
        {
            DateTime mydates = DateTime.Now;
            if (p_csvFile.Length > 7)
            {
                mydates = Convert.ToDateTime(string.Format("{0}-{1}-1"
                      , p_csvFile.Substring(8, 4)
                      , p_csvFile.Substring(12, 2))).AddDays(-1);
            }
            return mydates.ToShortDateString();
        }

//        public static System.Data.DataTable GetEIS(string p_eis, string p_connect)
//        {
//            return GetExcelData(getEISSQL(p_eis), p_connect);
//        }

//        protected static string getEISSQL(string p_table)
//        {
//            return string.Format(@"
//SELECT
//    LEFT(COST_CTR,3) AS COS
//    ,MID(COST_CTR,4,1) AS T
//    ,RIGHT(COST_CTR,1) AS [_CTR]
//    ,GL_TYPE 
//    ,GL_ACCT
//    ,CURRENCY_CODE
//    ,IE_BAL總計
//    ,IE_INT總計
//    ,IE_TRANS總計
//FROM [{0}$C5:J60000]
//WHERE MID(COST_CTR,4,1) IN ('1','A','C','D')
//", p_table);

//            /*,    ,MID(COST_CTR,4,1) AS T
//             * 

//    ,RIGHT(CODT_CTR,1) AS [_CTR]
                 
//    */
//        }

//        public static System.Data.DataTable GetMargin(string p_margin,string p_connect)
//        {
//            return GetExcelData(getMarginSQL(p_margin), p_connect);
//        }

//        protected static string getMarginSQL(string p_margin)
//        {
//            return string.Format(@"
//SELECT 
//    板塊 
//    ,LEFT([GL Account Number],10) AS [科子細目]
//    ,MID([GL Account Number],11) AS [名稱]
//    ,[Average Balance]
//    ,[Interest  Rate]
//    ,[Transfer Rate]
//FROM [{0}$B5:F1000]
//WHERE 板塊 IN ('1','A','C')
//AND [GL Account Number] <> 'Liability Accounts:'
//AND [GL Account Number] <> 'Totals for selected Liability Accounts'
//AND [GL Account Number] <> 'Asset Accounts:'
//AND [GL Account Number] <> 'Totals for selected Asset Accounts'
//AND [GL Account Number] NOT IN('1','A','C')
//", p_margin);
            
//        }

        public static string BeforeMonthLastDay(string p_ym)
        {
            DateTime dd = Convert.ToDateTime(string.Format("{0}-{1}-01", p_ym.Substring(0, 4)
                , p_ym.Substring(4, 2))).AddDays(-1);
            string month = dd.Month.ToString().Length < 2 ? "0" + dd.Month.ToString()
                : dd.Month.ToString();
            return string.Format("{0}{1}{2}", dd.Year.ToString(), month, dd.Day.ToString());
        }

        public static object MethodInvoke(object p_obj
            , string p_method,object[] p_para)
        {
            Type t = p_obj.GetType();
            object rtnObj = null;
            System.Reflection.MethodInfo mi = null;
            mi = t.GetMethod(p_method);
            if (mi != null)
            {
                 rtnObj = mi.Invoke(p_obj, p_para);
            }
            return rtnObj ;
        }

        /// <summary>
        /// 取回ARTMA、C、D 這三個CSV 的Schema
        /// </summary>
        /// <param name="p_fields">ARTMA、C、D這三個檔案的陣列</param>
        /// <param name="p_files">DataInside.GetCSVFields</param>
        /// <returns></returns>
        public static string GetARTMACDSchema(string[] p_files,string[] p_fields)
        {
            string[] ca = p_fields[0].ToString().Split(new char[] { ',' });
            string[] cc = p_fields[1].ToString().Split(new char[] { ',' });
            string[] cd = p_fields[2].ToString().Split(new char[] { ',' });
            return string.Format(
@"[{0}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{3}"" Double
Col8=""{4}"" Double

[{1}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{5}"" Double
Col8=""{6}"" Double
Col9=""{7}"" Double

[{2}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{8}"" Double
Col8=""{9}"" Double
Col9=""{10}"" Double
", p_files[0], p_files[1], p_files[2]
, ca[0], ca[1], cc[0], cc[1], cc[2], cd[0], cd[1], cd[2]);
        }

        public static string GetARTMABCDESchema(string[] p_files, string[] p_fields)
        {
            string[] ca = p_fields[0].ToString().Split(new char[] { ',' });
            string[] cc = p_fields[1].ToString().Split(new char[] { ',' });
            return string.Format(
@"[{0}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{3}"" Double
Col8=""{4}"" Double

[{1}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{3}"" Double
Col8=""{4}"" Double


[{2}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{5}"" Double
Col8=""{6}"" Double
Col9=""{7}"" Double

[{8}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{5}"" Double
Col8=""{6}"" Double
Col9=""{7}"" Double

[{9}]
CharacterSet=ANSI
ColNameHeader=True
Format=CSVDelimited
Col1=年度 Text
Col2=分行別 Text
Col3=幣別 Text 
Col4=大版塊 Text
Col5=小版塊 Text
Col6=科目 Text
Col7=""{5}"" Double
Col8=""{6}"" Double
Col9=""{7}"" Double

",p_files[0],p_files[1],p_files[2],ca[0],ca[1],cc[0],cc[1],cc[2],p_files[3],p_files[4]
 );


        }

        /// <summary>
        /// 取得因為月份改變的Range 位置
        /// </summary>
        /// <param name="p_col"></param>
        /// <param name="p_row"></param>
        /// <returns></returns>
        public static string GetNewRange(string p_col, string p_row)
        {
            //char[] chs = p_col.ToCharArray();
            //string rng = Convert.ToChar(Convert.ToInt16(chs[0])
            //    + DateTime.Now.AddMonths(-1).Month).ToString();

            //return string.Format("{0}{1}", rng, p_row);
            return Utility.GetNewRange(p_col, p_row
                , DateTime.Now.AddMonths(-1).Month);
        }


        public static string GetNewRange(string p_col, string p_row, int p_i)
        {
            char[] chs = p_col.ToCharArray();
            string rng = Convert.ToChar(Convert.ToInt16(chs[0]) 
                + p_i).ToString();
            return string.Format("{0}{1}", rng, p_row);        
        }

        /// <summary>
        /// 依檔案取得目前年月後的新Range
        /// </summary>
        /// <param name="p_col"></param>
        /// <param name="p_row"></param>
        /// <param name="p_source">傳入格式：10012_繳別達成率.xls</param>
        /// <param name="p_mark">檔案名中的年月的分隔符號</param>
        /// <returns></returns>
        public static string GetNewRange(string p_col, string p_row, string p_source,char p_mark)
        {
            string monthStr;
            monthStr = Path.GetFileName(p_source);
            monthStr = monthStr.Substring(0, monthStr.IndexOf(p_mark));
            monthStr = monthStr.Substring(3);
            return GetNewRange(p_col,p_row,Convert.ToInt16(monthStr));
        }
    }
}
